package com.srit.system.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.srit.core.bean.Page;
import com.srit.core.dao.BaseDao;
import com.srit.core.sql.BaseResultSetExtractor;
import com.srit.core.sql.OrderBy;
import com.srit.forecast.bean.WeatherCode;
import com.srit.forecast.constant.Constant;
import com.srit.system.bean.CodeType;
import com.srit.system.dao.CodeDao;
import com.srit.system.dao.CodeTypeDao;

@Service
@Transactional
public class CodeTypeDaoImpl implements CodeTypeDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private CodeDao codeDao;

	@Override
	public int getMaxId() {
		String sql = "SELECT MAX(ID) FROM TC_CODETYPE";
		return jdbcTemplate.queryForInt(sql);
	}

	@Override
	public void save(CodeType codeType) {
		String sql = "INSERT INTO TC_CODETYPE(ID,NAME,TYPE,DELETED,ENABLED,ORDERNO)VALUES(SEQ_TC_CODETYPE.NEXTVAL,?,?,?,?,?)";
		jdbcTemplate.update(
				sql,
				new Object[] { codeType.getName(), codeType.getType(),
						codeType.getDeleted(), codeType.getEnabled(),
						codeType.getOrderNo() });

	}

	@Override
	public CodeType getById(Integer id) {
		String sql = "select * from TC_CODETYPE where id=?";
		CodeType codeType = jdbcTemplate.queryForObject(sql,
				new Object[] { id }, new CodeTypeRowMapper());
		OrderBy orderBy = new OrderBy();
		// TODO:lazy
		if (codeType != null) {
			codeType.setCodeList(codeDao.listByCodeTypeId(codeType.getId(),
					orderBy));
		}
		return codeType;
	}

	@Override
	public void update(CodeType codeType) {
		String sql = "UPDATE  TC_CODETYPE SET NAME=?,TYPE=?,ENABLED=?,orderNo=? WHERE ID=?";
		jdbcTemplate.update(
				sql,
				new Object[] { codeType.getName(), codeType.getType(),
						codeType.getEnabled(), codeType.getOrderNo(),
						codeType.getId() });
	}

	@Override
	public void delete(Integer id) {
		String sql = "UPDATE  TC_CODETYPE SET DELETED=? WHERE ID=?";
		jdbcTemplate.update(sql, new Object[] { Constant.DELETED, id });
	}

	@Override
	public void page(Page page) {
		String sqlCount = "select count(*) from TC_CODETYPE where 1=1 ";
		String sqlAnd = " ";
		String sqlSelect = "select t2.* "
				+ "from (select rownum r,t1.* from TC_CODETYPE t1 where rownum<? "
				+ sqlAnd + ") t2 where t2.r>?" + Constant.SQL_ORDER_BY_ORDERNO;
		int count = jdbcTemplate.queryForInt(sqlCount + sqlAnd);
		List<WeatherCode> list = jdbcTemplate.query(sqlSelect, new Object[] {
				(page.getPageNum() + 1) * page.getPageSize(),
				page.getPageNum() * page.getPageSize() },
				new CodeTypeRowMapper());
		page.setCount(count);
		page.setList(list);
	}

	@Override
	public List<CodeType> listOnDrag() {
		String sql = "select * from TC_CODETYPE where ENABLED='Y' "
				+ Constant.SQL_ORDER_BY_ORDERNO;
		List<CodeType> result = jdbcTemplate
				.query(sql, new CodeTypeRowMapper());
		OrderBy orderBy = new OrderBy();
		orderBy.setSqlOrder("ORDER BY CODE ");
		for (Iterator<CodeType> it = result.iterator(); it.hasNext();) {
			CodeType codeType = it.next();
			codeType.setCodeList(codeDao.listByCodeTypeId(codeType.getId(),
					orderBy));
		}
		return result;
	}

	@Override
	public List<CodeType> listAll() {
		String sql = "select * from TC_CODETYPE";
		List<CodeType> result = jdbcTemplate
				.query(sql, new CodeTypeRowMapper());
		return result;
	}

}

class CodeTypeResultSetExtractor extends BaseResultSetExtractor<CodeType> {
}

class CodeTypeRowMapper implements RowMapper {

	@Override
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		CodeTypeResultSetExtractor extractor = new CodeTypeResultSetExtractor();
		return extractor.extractData(rs);
	}

}
